/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package personal.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import personal.obj.Order;
import personal.utility.DBUtility;

/**
 *
 * @author PHATNBSE61057
 */
public class OrderDAO implements Serializable {

    private final DBUtility utility = new DBUtility();

    public OrderDAO() {
    }

    public List<Order> getOrderByUsername(String usernameS) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Order order = null;
        List<Order> result = new ArrayList<Order>();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM [Order] WHERE Username = ? ORDER BY BookingTime";
            pst = con.prepareStatement(query);
            pst.setString(1, usernameS);
            rs = pst.executeQuery();
            while (rs.next()) {
                int orderID = rs.getInt("OrderID");
                Timestamp bookingTime = rs.getTimestamp("BookingTime");
                String username = rs.getString("Username");
                int statusID = rs.getInt("StatusID");
                double totalPrice = rs.getDouble("TotalPrice");
                order = new Order(orderID, bookingTime, username, statusID, totalPrice);
                result.add(order);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<Order> getOrderByInWeek(java.sql.Date dateFrom, java.sql.Date dateTo) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Order order = null;
        List<Order> result = new ArrayList<Order>();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM [Order] WHERE BookingTime >= ? AND BookingTime <= ? ORDER BY BookingTime DESC";
            pst = con.prepareStatement(query);
            pst.setDate(1, dateFrom);
            pst.setDate(2, dateTo);
            rs = pst.executeQuery();
            while (rs.next()) {
                int orderID = rs.getInt("OrderID");
                Timestamp bookingTime = rs.getTimestamp("BookingTime");
                String username = rs.getString("Username");
                int statusID = rs.getInt("StatusID");
                double totalPrice = rs.getDouble("TotalPrice");
                order = new Order(orderID, bookingTime, username, statusID, totalPrice);
                result.add(order);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public int getLastOrderID() {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            con = utility.makeConnection();
            String query = "SELECT TOP 1 OrderID FROM [Order] ORDER BY OrderID DESC";
            pst = con.prepareStatement(query);
            rs = pst.executeQuery();
            if (rs.next()) {
                int result = rs.getInt("OrderID");
                return result;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return -1;
    }

    public boolean insertOrder(Order orderInsert) {
        Connection con = null;
        PreparedStatement pst = null;
        int rowChanged;
        try {
            con = utility.makeConnection();
            String query = "INSERT INTO [Order] (BookingTime, Username, StatusID, TotalPrice) "
                    + "VALUES (?,?,?,?)";
            pst = con.prepareStatement(query);
            pst.setTimestamp(1, orderInsert.getBookingTime());
            pst.setString(2, orderInsert.getUsername());
            pst.setInt(3, orderInsert.getStatusID());
            pst.setDouble(4, orderInsert.getTotalPrice());
            rowChanged = pst.executeUpdate();
            if (rowChanged > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public boolean processOrder(int orderID) {
        Connection con = null;
        PreparedStatement pst = null;
        int rowChanged;
        try {
            con = utility.makeConnection();
            String query = "UPDATE [Order] SET StatusID = 2 WHERE OrderID = ?";
            pst = con.prepareStatement(query);
            pst.setInt(1, orderID);
            rowChanged = pst.executeUpdate();
            if (rowChanged > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }
}
